package it.univaq.mwt.arssa.business.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import it.univaq.mwt.arssa.business.BusinessException;
import it.univaq.mwt.arssa.business.RuoliService;
import it.univaq.mwt.arssa.business.model.Ruolo;

public class JDBCRuoliService implements RuoliService {
	
	private DataSource dataSource;
	
	public JDBCRuoliService(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public List<Ruolo> cercaRuoli() {
		
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<Ruolo> result = new ArrayList<Ruolo>();
		
		try {
			con = dataSource.getConnection();
			st = con.createStatement();
			rs = st.executeQuery("SELECT * FROM Ruoli ORDER BY ID DESC");
			while (rs.next()) {
				int id = rs.getInt("id");
				String codice = rs.getString("codice");
				String nome = rs.getString("nome");
				Double stipendio = rs.getDouble("stipendio");
				
				result.add(new Ruolo(id, codice, nome, stipendio));
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		return result;
	}

	@Override
	public Ruolo cercaRuolo(int id) {
		
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		Ruolo result = null;
		
		try {
			con = dataSource.getConnection();
			st = con.prepareStatement("SELECT * FROM Ruoli WHERE id=?");
			st.setInt(1, id);
			rs = st.executeQuery();
			if (rs.next()) {
				String codice = rs.getString("CODICE");
				String nome = rs.getString("NOME");
				Double stipendio = rs.getDouble("STIPENDIO");
				result = (new Ruolo(id, codice, nome, stipendio));
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		return result;
	}

	@Override
	public void inserisciRuolo(Ruolo ruolo) {

		Connection con = null;
		PreparedStatement st = null;
		
		try {
			con = dataSource.getConnection();
			st = con.prepareStatement("INSERT INTO Ruoli (id, codice, nome, stipendio) VALUES (NULL,?,?,?)");
			st.setString(1, ruolo.getCodice());
			st.setString(2, ruolo.getNome());
			st.setDouble(3, ruolo.getStipendio());
			st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}

	}
	
	
	
	@Override
	public void modificaRuolo(Ruolo ruolo) {
		
		Connection con = null;
		PreparedStatement st = null;
		
		try {					
			con = dataSource.getConnection();	
			
			st = con.prepareStatement("UPDATE RUOLI SET NOME = ?, CODICE = ?, STIPENDIO = ?  WHERE (ID = ?)");		
			st.setString(1, ruolo.getNome());
			st.setString(2, ruolo.getCodice());
			st.setDouble(3, ruolo.getStipendio());
			st.setInt(4, ruolo.getId());
			st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
	}

	@Override
	public boolean verificaCodice(String codice) {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			con = dataSource.getConnection();
			st = con.prepareStatement("SELECT * FROM RUOLI WHERE CODICE=?");
			st.setString(1, codice);
			rs = st.executeQuery();
			
			if(rs.next()){
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		return false;
	}
	
	@Override
	public boolean verificaNome(String nome) {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			con = dataSource.getConnection();
			st = con.prepareStatement("SELECT * FROM RUOLI WHERE NOME=?");
			st.setString(1, nome);
			rs = st.executeQuery();
			
			if(rs.next()){
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		return false;
	}

	@Override
	public void eliminaRuolo(int id) {

		Connection con = null;
		PreparedStatement st = null;
		
		try {					
			con = dataSource.getConnection();	
			
			st = con.prepareStatement("DELETE FROM RUOLI WHERE (ID = ?)");		
			st.setInt(1, id);
			st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e);
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}

		}
		
	}
	
	

}
